﻿:setvar SourceDatabaseName "AdventureWorks2012"
:setvar DestinationDatabaseName "AdventureWorks2012_VirtualDB"
--increase initial size of database to reduce allocation time
USE [master]
GO
ALTER DATABASE [$(DestinationDatabaseName)] MODIFY FILE ( NAME = N'$(DestinationDatabaseName)', FILEGROWTH = 51200KB, SIZE = 921600KB )
GO
ALTER DATABASE [$(DestinationDatabaseName)] MODIFY FILE ( NAME = N'$(DestinationDatabaseName)_log', SIZE = 921600KB )
GO


USE $(DestinationDatabaseName)
GO
--clean data 
TRUNCATE TABLE lov.Value;
DELETE FROM lov.[Row];
DELETE FROM lov.Field;
DELETE FROM lov.Entity;
DISABLE TRIGGER [lov].[iuTrig_Field] ON [lov].[Field]
GO

USE $(SourceDatabaseName)
GO
--populate metadata
INSERT INTO $(DestinationDatabaseName).lov.Entity([Schema],Name,[Description])
SELECT --TOP 10
	CONVERT(VARCHAR(100),SCHEMA_NAME(st.[schema_id]))
	, CONVERT(VARCHAR(255),st.name)
	, CONVERT(VARCHAR(255),ep.Value) as [Description]
FROM sys.tables st
LEFT OUTER JOIN [sys].[extended_properties] ep ON st.[object_id] = ep.major_id 
	AND ep.minor_id = 0
	AND ep.class_desc ='OBJECT_OR_COLUMN'
	AND ep.name ='MS_Description'
WHERE SCHEMA_NAME(st.[schema_id])!= 'dbo'
	--and SCHEMA_NAME(st.[schema_id])='HumanResources'
ORDER BY 1,2
GO
INSERT INTO $(DestinationDatabaseName).lov.Field ([EntityId], [Name], [DataType], [Description], [SortOrder], [Nullable], [PrimaryKey])
SELECT
	e.Id as EntityId
	, CONVERT(VARCHAR(255),sc.name) as FieldName
	,CONVERT(VARCHAR(100), TYPE_NAME(sc.user_type_id)
		+(CASE WHEN COLUMNPROPERTY(st.[object_id], sc.name,'IsPrecise') IS NULL THEN '' ELSE '('
			+CONVERT(VARCHAR(10),COLUMNPROPERTY(st.[object_id], sc.name,'PRECISION'))
			+isnull(','+CONVERT(VARCHAR(10),COLUMNPROPERTY(sc.[object_id], sc.name,'SCALE')),'')+ ')' END)
		+(CASE WHEN sc.is_ansi_padded !=1 OR sc.user_type_id !=sc.system_type_id then '' ELSE 
			'('+ (CASE sc.max_length WHEN -1 THEN 'max' ELSE CONVERT(VARCHAR(10),sc.max_length/(CASE WHEN TYPE_NAME(sc.system_type_id) LIKE 'n%' THEN 2 ELSE 1 END)) END )+')' END)
	) as DataType
	,CONVERT(varchar(255),ep.Value) as Description
	,sc.column_id  as SortOrder, sc.is_nullable as Nullable, i.is_primary_key as PrimaryKey
FROM [sys].[tables] st
INNER JOIN $(DestinationDatabaseName).lov.Entity e on CONVERT(VARCHAR(100),SCHEMA_NAME(st.[schema_id])) = e.[Schema]
	AND CONVERT(VARCHAR(255),st.name) COLLATE Latin1_General_CI_AS = CONVERT(VARCHAR(255),e.Name) COLLATE Latin1_General_CI_AS 
INNER JOIN [sys].[columns] sc on st.[object_id] = sc.[object_id]
	and sc.is_computed=0 
LEFT OUTER JOIN [sys].[extended_properties] ep ON st.[object_id] = ep.major_id 
	AND sc.[column_id] = ep.minor_id 
	AND ep.class_desc ='OBJECT_OR_COLUMN'
	AND ep.name ='MS_Description'
LEFT OUTER JOIN [sys].[indexes] AS i
		INNER JOIN [sys].[index_columns] AS ic ON  i.[object_id] = ic.[object_id]
	ON st.[object_id]=i.[object_id] AND i.is_primary_key = 1
			AND i.index_id = ic.index_id AND sc.column_id = ic.column_id
ORDER BY e.Id, sc.column_id 
GO
--convert user defined data types to system types
UPDATE $(DestinationDatabaseName).lov.Field SET DataType = 'nvarchar(15)' WHERE DataType = 'AccountNumber';
UPDATE $(DestinationDatabaseName).lov.Field SET DataType = 'nvarchar(50)' WHERE DataType = 'Name';
UPDATE $(DestinationDatabaseName).lov.Field SET DataType = 'bit' WHERE DataType IN ('Flag','NameStyle');
UPDATE $(DestinationDatabaseName).lov.Field SET DataType = 'nvarchar(25)' WHERE DataType IN ('OrderNumber','Phone');

GO
--add views and triggers
USE [$(DestinationDatabaseName)]
GO
EXEC [lov].[usp_GenerateEntities] '%', Null, 2 --execute only
GO


--populate data from $(SourceDatabaseName)
USE [$(SourceDatabaseName)]
GO
DECLARE @SQL nvarchar(max)= '', @FieldList varchar(max),@EntityId int, @EntitySchema varchar(100), @EntityName varchar(255)
DECLARE c_Entities CURSOR LOCAL FORWARD_ONLY STATIC  READ_ONLY  FOR
	SELECT Id, e.[Schema],e.[Name]
	FROM $(DestinationDatabaseName).lov.Entity e
	ORDER BY e.[Schema],e.[Name]

	OPEN c_Entities 
	FETCH NEXT FROM c_Entities INTO @EntityId, @EntitySchema, @EntityName 
	WHILE (@@FETCH_STATUS<>-1)
	BEGIN
		IF (@@FETCH_STATUS<>-2)
		BEGIN
			SET @FieldList = ','
			SELECT @FieldList += ',['+f.Name+']'
			FROM $(DestinationDatabaseName).lov.Field f
			WHERE f.EntityId = @EntityId
			SET @FieldList = REPLACE(@FieldList,',,','')
			
			SET @SQL = 'INSERT INTO $(DestinationDatabaseName).['+@EntitySchema+'].['+@EntityName +']('+@FieldList+') 
SELECT '+@FieldList+'
FROM ['+@EntitySchema+'].['+@EntityName+'];'

			PRINT @SQL
			EXEC sp_executesql @SQL
			
		FETCH NEXT FROM c_Entities INTO @EntityId, @EntitySchema, @EntityName 
	END --if (@@FETCH_STATUS<>-2)
END -- (@@FETCH_STATUS<>-1)
CLOSE c_Entities
DEALLOCATE c_Entities
GO

--enable triggers and shrink log file 
USE [$(DestinationDatabaseName)]
GO
ENABLE TRIGGER [lov].[iuTrig_Field] ON [lov].[Field];
GO
DBCC SHRINKFILE (N'$(DestinationDatabaseName)_log' , 0, TRUNCATEONLY)
GO

--test results are printed in output
/*
...
(2 row(s) affected)
--running script...
--done!
INSERT INTO AdventureWorks2012_VirtualDB.[HumanResources].[Department]([DepartmentID],[GroupName],[ModifiedDate],[Name]) 
SELECT [DepartmentID],[GroupName],[ModifiedDate],[Name]
FROM [HumanResources].[Department];
......
(538 row(s) affected)
INSERT INTO AdventureWorks2012_VirtualDB.[Sales].[Store]([BusinessEntityID],[Demographics],[ModifiedDate],[Name],[rowguid],[SalesPersonID]) 
SELECT [BusinessEntityID],[Demographics],[ModifiedDate],[Name],[rowguid],[SalesPersonID]
FROM [Sales].[Store];

(701 row(s) affected)

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

--dbstatistics
DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
8	2	128	128	128	128



--unit test
--try to add it twice. Second insert is ignored by Guard index
insert into lov.Entity ([Schema], [Name], [Description]) 
	values('dbo','Test','Test table')
select * from lov.Entity;

--try to add it twice. Second insert is ignored by Guard index
insert into lov.Field(EntityId, [Name], [DataType], [SortOrder], [Nullable], [Description]) 
	values (1,'Id','int', 1, 0, 'field Id description')
		,(1,'Name','varchar(32)', 2, 1, 'field Name description')
select * from lov.Field;

--add some data
insert into dbo.Test (Id, Name) values (1, 'Test1')
select * from dbo.Test;
declare @RowId uniqueidentifier = '888C5E4C-4031-40BE-9EE5-4EBB46ADDC4A'
select * from lov.Row --where Id = @RowId 
select * from lov.Value --where RowId = @RowId
--soft delete
delete from dbo.Test --where Id = 1
--recilce bin
delete from lov.Row --where Id = @RowId
*/